package org.iabako.server.serviceimpl.importdata;

import org.apache.log4j.Logger;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.iabako.server.dao.business.ClientDAO;
import org.iabako.server.dao.business.CustomFieldDAO;
import org.iabako.server.dao.user.EnterpriseDAO;
import org.iabako.server.tools.BundleMessages;
import org.iabako.shared.entity.business.*;
import org.iabako.shared.entity.enumeration.Country;
import org.iabako.shared.entity.enumeration.Gender;
import org.iabako.shared.entity.enumeration.Language;
import org.iabako.shared.entity.user.Enterprise;
import org.iabako.shared.iservice.business.ClientService;
import org.iabako.shared.iservice.importdata.ImportClientService;
import org.iabako.shared.tools.GenericTools;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;

import javax.servlet.http.HttpSession;
import javax.validation.ConstraintViolation;
import javax.validation.Validation;
import javax.validation.Validator;
import java.io.File;
import java.io.FileInputStream;
import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Created by jose on 17/02/15.
 */

@org.springframework.stereotype.Service("importClientService")
public class ImportClientServiceImpl implements ImportClientService {

    @Autowired
    EnterpriseDAO enterpriseDAO;
    @Autowired
    ClientDAO clientDAO;
    @Autowired
    ClientService clientService;
    @Autowired
    CustomFieldDAO customFieldDAO;
    @Autowired
    BundleMessages messages;

    static final String EXCEL_ALPHABET = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";

    static Logger log = Logger.getLogger(ImportClientServiceImpl.class.getName());

    @Transactional(propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
    public String readFile(boolean simulate, String importFileNameInServerSession) {

        HttpSession session = (((ServletRequestAttributes) RequestContextHolder.
                currentRequestAttributes()).getRequest()).getSession();

        File importFile = (File) session.getAttribute(importFileNameInServerSession);
        if (importFile != null) {
            session.removeAttribute(importFileNameInServerSession);
        } else {
            return "\nERROR : No file submitted";
        }

        List<String> messages = new ArrayList<String>();
        if (simulate) {
            messages.add("\n****** THIS IS A SIMULATION. NO CHANGES WILL BE MADE IN DATABASE ******\n");
        }

        try {
            XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(importFile));
            Long enterpriseId = null;
            try {
                enterpriseId = Long.parseLong(getValue(workbook.getSheetAt(0).getRow(0), 0));
            } catch (IllegalArgumentException e) {
            }

            String enterpriseName = getValue(workbook.getSheetAt(0).getRow(0), 1);
            if (enterpriseId == null || enterpriseName == null) {
                return "\nERROR : Enterprise ID and NAME were not found as expected in sheet 1";
            }

            Enterprise enterprise = enterpriseDAO.findById(enterpriseId);
            if (!enterprise.getName().equals(enterpriseName)) {
                return "\nERROR : Enterprise id and name do not match";
            }

            Sheet dataSheet = workbook.getSheetAt(1);
            Map<String, Client> clientList = new HashMap<String, Client>();

            int rowIndex = 3;
            Row row = dataSheet.getRow(rowIndex);
            int columnIndex = 0;

            List<CustomField> customFields = customFieldDAO.getCustomFields(enterprise);
            Map<Integer, CustomField> customFieldMap = customFieldVerification(dataSheet, 15, customFields, messages);
            if (customFieldMap == null) {
                messages.add("\n/!\\ Clients will not be imported because there are errors in import file\n");
                return printMessagesList(messages);
            }

            boolean errorFound = false;

            Validator validator = Validation.buildDefaultValidatorFactory().getValidator();

            while (getValue(row, columnIndex) != null) {

                Client client = new Client();
                client.setNumber(getValue(row, columnIndex++));
                if (clientList.get(client.getNumber()) != null) {
                    errorFound = true;
                    messages.add("ERROR in line " + (rowIndex + 1) + " (column " + getColumnLetter(columnIndex) + ") : This client ID is already used in this file");
                }

                String genderString = getValue(row, columnIndex++);
                try {
                    client.setGender(genderString != null ? Gender.valueOf(genderString) : null);
                } catch (IllegalArgumentException e) {
                    errorFound = true;
                    messages.add("ERROR in line " + (rowIndex + 1) + " (column " + getColumnLetter(columnIndex) + ") : Gender value " + genderString + " is unknown. It should be \"man\" or \"woman\"");
                }

                client.setFirstName(getValue(row, columnIndex++));
                client.setLastName(getValue(row, columnIndex++));
                //Calculated name mandatory field
                client.getName();
                client.setEmail(getValue(row, columnIndex++));

                try {
                    int day = Integer.parseInt(getValue(row, columnIndex));
                    int month = Integer.parseInt(getValue(row, columnIndex + 1));
                    int year = Integer.parseInt(getValue(row, columnIndex + 2));

                    SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy");
                    sdf.setLenient(false);
                    Date date = sdf.parse(day + "/" + month + "/" + year);
                    client.setBirthday(date);
                } catch (NumberFormatException e) {
                    errorFound = true;
                    messages.add("ERROR in line " + (rowIndex + 1) + " (column " + getColumnLetter(columnIndex) + ") : Day, Month and Year should be number values");
                } catch (ParseException e) {
                    errorFound = true;
                    messages.add("ERROR in line " + (rowIndex + 1) + " (column " + getColumnLetter(columnIndex) + ") : Birthday date is wrong");
                }
                columnIndex += 3;

                Address address = new Address();
                address.setAddressLine1(getValue(row, columnIndex++));
                address.setAddressLine2(getValue(row, columnIndex++));

                String countryString = getValue(row, columnIndex++);
                try {
                    address.setCountry(countryString != null ? Country.valueOf(countryString) : null);
                } catch (IllegalArgumentException e) {
                    errorFound = true;
                    messages.add("ERROR in line " + (rowIndex + 1) + " (column " + getColumnLetter(columnIndex) + ") : Country value \"" + countryString + "\" is unknown. Ask developers for the valid country values list");
                }

                address.setCity(getValue(row, columnIndex++));
                address.setZipCode(getValue(row, columnIndex++));

                client.setAddress(address);

                client.setPhoneNumber1(getValue(row, columnIndex++));
                client.setPhoneNumber2(getValue(row, columnIndex++));

                Set<ConstraintViolation<Client>> violations = validator.validate(client);

                if (!violations.isEmpty()) {
                    errorFound = true;
                    for (ConstraintViolation<Client> c : violations) {
                        if ("{javax.validation.constraints.NotNull.message}".equals(c.getMessageTemplate())) {
                            messages.add("ERROR in line " + (rowIndex + 1) + " : \"" + c.getPropertyPath() + "\" property is mandatory");

                        } else {
                            messages.add("ERROR in line " + (rowIndex + 1) + " : " + this.messages.getLabel(Language.EN, c.getMessage()));
                        }
                    }
                }

                //technical fields
                client.setEnterprise(enterprise);

                if (!customFieldMap.isEmpty()) {
                    List<CustomFieldValue> customFieldValues = getCustomFieldValues(row, columnIndex, customFieldMap, messages);
                    client.setCustomFieldValues(customFieldValues);
                    errorFound = errorFound || customFieldValues == null;
                }

                clientList.put(client.getNumber(), client);

                columnIndex = 0;
                row = dataSheet.getRow(++rowIndex);
            }

            messages.add("\nINFO : Last line read in import file : " + rowIndex);

            if (clientList.size() == 0) {
                messages.add("\nERROR : No clients found in import file");
                return printMessagesList(messages);
            }

            List<String> notUniqueNumbers = clientDAO.getNotUniqueNumbers(enterprise, clientList.keySet());
            if (!notUniqueNumbers.isEmpty()) {
                errorFound = true;
                String notUniqueNumberAsString = "";
                for (String number : notUniqueNumbers) {
                    notUniqueNumberAsString += number + ", ";
                }
                notUniqueNumberAsString = notUniqueNumberAsString.substring(0, notUniqueNumberAsString.length() - 2);
                messages.add("ERROR : These client IDs are already used in database : " + notUniqueNumberAsString);
            }

            if (errorFound) {
                messages.add("\n/!\\ Clients will not be imported because there are errors in import file\n");
                return printMessagesList(messages);
            }

            if (!simulate) {
                for (Client client : clientList.values()) {
                    List<CustomFieldValue> customFieldValues = client.getCustomFieldValues();
                    //To avoid cascading and double persist
                    client.setCustomFieldValues(null);
                    clientService.save(client, customFieldValues);
                }
            }

            messages.add("\nClient import processed successfully : " + clientList.size() + " clients have been imported for " + enterprise.getName() + "\n");

            return printMessagesList(messages);

        } catch (Exception e) {
            if (e.getMessage() != null && e.getMessage().contains("InvalidFormatException")) {
                return "ERROR : Document submitted is not a valid XLSX file";
            }
            log.error(e.getMessage(), e);
            return "ERROR : " + e.getMessage();
        }
    }

    private List<CustomFieldValue> getCustomFieldValues(Row row, int columnIndex, Map<Integer, CustomField> customFieldMap, List<String> messages) {
        List<CustomFieldValue> customFieldValues = new ArrayList<CustomFieldValue>();

        int lastColumnIndex = columnIndex + customFieldMap.size();

        boolean errorFound = false;
        for (; columnIndex < lastColumnIndex; columnIndex++) {

            String stringValue = getValue(row, columnIndex);
            if (GenericTools.isEmpty(stringValue)) {
                continue;
            }

            CustomField customField = customFieldMap.get(columnIndex);
            CustomFieldValue value = new CustomFieldValue();
            value.setCustomField(customField);

            try {
                switch (customFieldMap.get(columnIndex).getCustomFieldType()) {
                    case integer:
                        value.setIntegerValue(Long.parseLong(stringValue));
                        break;
                    case decimal:
                        value.setDoubleValue(Double.parseDouble(stringValue));
                        break;
                    case list:
                        if (customField.getCustomFieldListItems().isEmpty()) {
                            customField.setCustomFieldListItems(customFieldDAO.getCustomFieldsItemValues(customField));
                        }
                        boolean found = false;
                        for (CustomFieldListItem item : customField.getCustomFieldListItems()) {
                            if (item.getLabel().equals(stringValue)) {
                                value.setListItemValue(item);
                                found = true;
                                break;
                            }
                        }
                        if (!found) {
                            messages.add("ERROR in line " + (row.getRowNum() + 1) + " (column " + getColumnLetter(columnIndex) + ") : " +
                                    "Value \"" + stringValue + "\" can not be assigned to custom field \"" + customField.getTitle() + "\". " +
                                    "Please verify if this value below to this LIST of values");
                            errorFound = true;
                        }
                        break;
                    case date:
                        value.setDateValue(GenericTools.getDate(stringValue));
                        if (value.getDateValue() == null) {
                            messages.add("ERROR in line " + (row.getRowNum() + 1) + " (column " + getColumnLetter(columnIndex) + ") : " +
                                    "Value \"" + stringValue + "\" can not be assigned to custom field \"" + customField.getTitle() + "\". " +
                                    "Please verify if this is a valid DATE value");
                            errorFound = true;
                        }
                        break;
                    case inputText:
                    case textarea:
                        value.setStringValue(stringValue);
                        break;
                }
            } catch (Exception e) {
                messages.add("ERROR in line " + (row.getRowNum() + 1) + " (column " + getColumnLetter(columnIndex) + ") : " +
                        "Value \"" + stringValue + "\" can not be assigned to custom field \"" + customField.getTitle() + "\". " +
                        "Please verify if this value can be converted into " + customField.getCustomFieldType());
                errorFound = true;
            }
            customFieldValues.add(value);
        }
        return errorFound ? null : customFieldValues;
    }

    private Map<Integer, CustomField> customFieldVerification(Sheet sheet, int columnIndex, List<CustomField> customFields, List<String> messages) {
        Map<Integer, CustomField> result = new HashMap<Integer, CustomField>();
        Row labelRow = sheet.getRow(1);
        int firstColumnIndex = columnIndex;

        Cell data = labelRow.getCell(columnIndex);
        if (data == null) {
            messages.add("INFO : No custom fields found in import file");
            return result;
        }

        boolean errorFound = false;

        List<String> customFieldInFile = new ArrayList<String>();

        while (data != null) {
            customFieldInFile.add(getValue(labelRow, columnIndex++));
            data = labelRow.getCell(columnIndex);
        }

        String lastCustomField = customFieldInFile.get(customFieldInFile.size() - 1);
        lastCustomField = GenericTools.isEmpty(lastCustomField) ? "empty value" : lastCustomField;
        lastCustomField = "column " + getColumnLetter(columnIndex) + " (" + lastCustomField + ")";
        messages.add("INFO : Last custom field read in import file : " + lastCustomField + "\n");

        for (String cFInFile : customFieldInFile) {
            boolean found = false;

            for (CustomField customField : customFields) {
                if (customField.getTitle().equals(cFInFile)) {
                    found = true;
                    result.put(customFieldInFile.indexOf(cFInFile) + firstColumnIndex, customField);
                    break;
                }
            }

            if (!found) {
                messages.add("ERROR : Custom field \"" + cFInFile + "\" does not exist");
                errorFound = true;
            }
        }

        for (CustomField customField : customFields) {
            boolean found = false;

            for (String cFInFile : customFieldInFile) {
                if (customField.getTitle().equals(cFInFile)) {
                    found = true;
                    break;
                }
            }

            if (!found) {
                messages.add("WARN : There is no column for custom field \"" + customField.getTitle() + "\" in import file");
            }
        }

        return errorFound ? null : result;
    }

    private String printMessagesList(List<String> messages) {
        String messagesAsString = "\n";
        for (String msg : messages) {
            messagesAsString += msg + "\n";
        }
        return messagesAsString;
    }

    private String getColumnLetter(int columnIndex) {
        try {
            return EXCEL_ALPHABET.charAt(columnIndex == 0 ? 0 : columnIndex - 1) + "";
        } catch (IndexOutOfBoundsException e) {
            return columnIndex + "";
        }
    }

    private String getValue(Row row, int columnIndex) {
        if (row == null || row.getCell(columnIndex) == null) {
            return null;
        }
        String value = null;
        int indexCellGetter = 0;
        while (value == null) {
            try {
                switch (indexCellGetter) {
                    case 0:
                        value = row.getCell(columnIndex).getStringCellValue();
                        break;
                    case 1:
                        double doubleValue = row.getCell(columnIndex).getNumericCellValue();
                        int intValue = new Double(doubleValue).intValue();
                        if (doubleValue - new Double(doubleValue).intValue() > 0) {
                            value = new BigDecimal(doubleValue) + "";
                        } else {
                            value = intValue + "";
                        }
                        break;
                    case 2:
                        value = row.getCell(columnIndex).getBooleanCellValue() + "";
                        break;
                    case 3:
                        value = GenericTools.formatDate(row.getCell(columnIndex).getDateCellValue());
                        break;
                    case 4:
                        value = "";
                }
            } catch (IllegalStateException e) {
                indexCellGetter++;
            }
        }
        return value.isEmpty() ? null : value;
    }
}
